# coding: utf-8

from sqlalchemy.sql import text

from app.extensions import db
from app.models import BaseModel
from ..constant import Choice
from .constant import (
    CallResultDisplay, VirtualNumberValid, VirtualSmsMessageResult,
)


class NumberPoolForLT(BaseModel):
    """
    created by chenhj at 2017/10/10
    联通号码池
    """
    __tablename__ = 'number_pool_for_lt'

    search_keys = ('tel',)

    area_id = db.Column(db.ForeignKey('area.id'))
    tel = db.Column(db.String(128), nullable=False, unique=True)
    # 当前有效使用次数
    use_count = db.Column(db.Integer, default=0)
    # 专用于公司首页，对应公司的ID
    enterprise_id = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 内部保留，如被sem短信使用，企业推广首页使用
    reserved = db.Column(db.Boolean, default=False, server_default=text('FALSE'))

    area = db.relationship('Area', backref='numbers_lt')
    enterprise = db.relationship(
        'Enterprise',
        primaryjoin='foreign(NumberPoolForLT.enterprise_id) == remote(Enterprise.id)',
        single_parent=True,
    )

    @property
    def enterprise_name(self):
        if self.enterprise:
            return self.enterprise.remark or self.enterprise.enterprise_name
        return ''

    @classmethod
    def get_available_number(cls, area_id, default_area='440300'):
        """
        create by ld_wu 2017-11-01
        :param area_id: 所在城市id
        :param default_area: 默认的城市id，默认值为深圳市
        :return:
        """
        # 优先取该外呼号码本地的虚拟号码, 否则取深圳的虚拟号, 再没有则不按地区取
        virtual_number = cls.query.filter_by(
            use_count=0,
            area_id=area_id,
            deleted_at=0,
            enterprise_id=0,
            reserved=False,
        ).order_by(
            cls.updated_at
        ).first() or cls.query.filter_by(
            use_count=0,
            area_id=default_area,
            deleted_at=0,
            enterprise_id=0,
            reserved=False,
        ).order_by(
            cls.updated_at
        ).first() or cls.query.filter_by(
            use_count=0,
            deleted_at=0,
            enterprise_id=0,
            reserved=False,
        ).order_by(
            cls.updated_at
        ).first()
        return virtual_number

    @classmethod
    def get_substitute_number(cls, tel_a, tel_b='', area_id='', invalid_area=''):
        """
        add by zhouhl 2018-04-02
        当禁用虚拟号时，寻找可用的替代虚拟号
        """
        tel_a_using_vir_nums, tel_b_using_vir_nums = [], []
        tel_a_using_vir_nums = [
            i.tel_x for i in VirtualNumber.query.filter_by(
                tel_a=tel_a,
                valid=VirtualNumberValid.VALID.value
            )
        ]
        if tel_b:
            tel_b_using_vir_nums = [
                i.tel_x for i in VirtualNumber.query.filter_by(
                    tel_b=tel_b,
                    valid=VirtualNumberValid.VALID.value
                )
            ]
        using_vir_nums = tel_a_using_vir_nums + tel_b_using_vir_nums
        virtual_number = cls.query.filter(
            cls.tel.notin_(using_vir_nums),
            cls.deleted_at==0,
            cls.area_id==area_id,
            cls.enterprise_id==0,
            cls.reserved==False,
        ).order_by(
            cls.use_count,
            cls.updated_at
        ).first() \
        or cls.query.filter(
            cls.tel.notin_(using_vir_nums),
            cls.deleted_at==0,
            cls.area_id!=invalid_area,
            cls.enterprise_id==0,
            cls.reserved==False,
        ).order_by(
            cls.use_count,
            cls.updated_at
        ).first()
        return virtual_number

    @classmethod
    def bind_for_enterprise(cls, enterprise_id, bind_num):
        """
        add by zhouhl 2018-04-10
        指定某个虚拟号，绑定给金牌/银牌，用作品牌首页推广
        如果此号码存在其他虚拟关系，则需要替换成其他可用号码
        根据返回结果判断绑定是否成功，为None时绑定成功，否则为报错信息
        """
        if bind_num == '':
            existed_bind_virtual_number = cls.query.filter(
                cls.enterprise_id==enterprise_id,
                cls.deleted_at==0
            ).first()
            if existed_bind_virtual_number:
                existed_bind_virtual_number.enterprise_id = 0
            return None

        bind_virtual_number = cls.query.filter(
            cls.tel==bind_num,
            cls.deleted_at==0
        ).first()

        # STEP 0.该号码是否存在
        if not bind_virtual_number:
            error_msg = '该号码不存在，请检查'
            return error_msg

        # STEP 1.该号码是否被其他金牌/银牌使用
        if (
            bind_virtual_number.enterprise_id
            and bind_virtual_number.enterprise_id != enterprise_id
        ):
            error_msg = '该号码已被其他金牌/银牌使用'
            return error_msg

        # STEP 2.该号码是否用作sem推广的短信发送
        if (
            bind_virtual_number.reserved
            and bind_virtual_number.enterprise_id != enterprise_id
        ):
            error_msg = '该号码正被内部使用(如SEM推广)，请联系管理员处理'
            return error_msg

        # STEP 3.该号码是否用作 项目的axb关系 or 客服系统外呼axn
        using_virtual_number_list = VirtualNumber.get_list_by_args(
            valid=VirtualNumberValid.VALID.value,
            tel_x=bind_num,
            for_sms=False
        )

        for each in using_virtual_number_list:
            substitute_number = cls.get_substitute_number(
                tel_a=each.tel_a,
                tel_b=each.tel_b,
                area_id=bind_virtual_number.area_id,
            )
            if substitute_number:
                each.tel_x = substitute_number.tel
                bind_virtual_number.use_count -= 1
                substitute_number.use_count += 1
            else:
                error_msg = '{bind_num} 存在替换失败的虚拟号关系'.format(
                    bind_num=bind_num)
                return error_msg

        # STEP 4.如果该企业还绑定了其他虚拟号，则解除关系
        existed_bind_virtual_number = cls.query.filter(
            cls.enterprise_id==enterprise_id,
            cls.deleted_at==0
        ).first()
        if existed_bind_virtual_number:
            existed_bind_virtual_number.enterprise_id = 0
            existed_bind_virtual_number.reserved = False
            # 还需要解绑 axn 关系
            ex_bind_num = existed_bind_virtual_number.tel
            ex_virtual_number_list = VirtualNumber.get_list_by_args(
                valid=VirtualNumberValid.VALID.value,
                tel_b='',
                tel_x=ex_bind_num,
                for_sms=False
            )
            for each in ex_virtual_number_list:
                each.valid = VirtualNumberValid.INVALID.value

        # STEP 5.该号码修改为企业首页推广专用，且标识为内部保留号码
        # NOTE by zhouhl 企业推广首页的虚拟号不写 VirtualNumber 记录
        # 在呼叫查询号码时，直接判断该虚拟号是否被企业推广使用
        bind_virtual_number.enterprise_id = enterprise_id
        bind_virtual_number.reserved = True

        return None


class VirtualNumber(BaseModel):
    """
    add by zhouhl 2017-09-13
    虚拟号记录
    """
    __tablename__ = 'virtual_numbers'

    filter_dict = {
        'tel_a': (),
        'tel_b': (),
        'tel_x': (),
        'valid': (),
        'demand_id': (),
        'created_at_begin': ('created_at', '>='),
        'created_at_end': ('created_at', '<='),
        'updated_at_begin': ('updated_at', '>='),
        'updated_at_end': ('updated_at', '<='),

    }

    # 增加user_id，demand_id，方便关联查询,
    # 用于给项目发布绑定虚拟号用, 在拉新功能绑定虚拟号时不同步这两个字段 add by chenhj
    user_id = db.Column(db.Integer, server_default=text("'0'"), default=0)
    demand_id = db.Column(
        db.Integer,
        server_default=text("'0'"),
        default=0,
        index=True
    )
    # 号码A
    tel_a = db.Column(db.String(16), nullable=False, index=True)
    # 号码B，AXN模式下，可以为空
    tel_b = db.Column(
        db.String(16),
        default='',
        server_default=text("''"),
        index=True
    )
    # 虚拟号
    tel_x = db.Column(db.String(16), nullable=False, index=True)
    # 是否有效
    valid = db.Column(
        db.Integer,
        default=VirtualNumberValid.VALID.value,
        server_default=text("'1'")
    )
    # 是否为短信
    for_sms = db.Column(db.Boolean(), default=False, server_default=text("false"))
    # 内部保留，如被sem短信使用，企业推广首页使用
    reserved = db.Column(db.Boolean(), default=False, server_default=text('FALSE'))

    @property
    def valid_display(self):
        return VirtualNumberValid(self.valid).display

    @classmethod
    def unbind_virtual_by_partic(cls, partic):
        virtual_num_data = VirtualNumber.get_by_args(
            demand_id=partic.demand_id,
            tel_b=partic.owner.mobile,
            valid=VirtualNumberValid.VALID.value
        )
        if virtual_num_data:
            virtual_num_data.valid = VirtualNumberValid.INVALID.value
            number = NumberPoolForLT.get_by_args(tel=virtual_num_data.tel_x)
            if number:
                number.use_count -= 1

    @classmethod
    def unbind_virtual_by_id(cls, id):
        """
        add by zhouhl 2018-03-13
        指定虚拟号关系id，让该记录失效
        """
        virtual_num = VirtualNumber.get_by_id(id)
        if virtual_num:
            virtual_num.valid = VirtualNumberValid.INVALID.value
            number = NumberPoolForLT.get_by_args(tel=virtual_num.tel_x)
            if number:
                number.use_count -= 1


class CallOrderForLT(BaseModel):
    """
    add by zhouhl 2017-09-13
    联通话单推送
    """
    __tablename__ = 'call_order_for_lt'

    filter_dict = {
        'outer_user': (),
        'caller': (),
        'called': (),
        'call_result': (),
    }

    # 主叫真实号码
    caller = db.Column(db.String(128), default='', server_default=text("''"))
    # 主叫分配号码，即被叫看到的号显
    caller_show = db.Column(db.String(128), default='', server_default=text("''"))
    # 主叫地区
    caller_area = db.Column(db.String(128), default='', server_default=text("''"))

    # 被叫真实号码
    called = db.Column(db.String(128), default='', server_default=text("''"))
    # 被叫分配号码，即主叫所拨打的虚拟号
    called_show = db.Column(db.String(128), default='', server_default=text("''"))
    # 被叫地区
    called_area = db.Column(db.String(128), default='', server_default=text("''"))

    # 通话状态，通话状态的取值请查通话状态区分
    call_result = db.Column(db.String(128), default='', server_default=text("''"))
    # 该次通话费用，单位为元
    call_cost = db.Column(db.String(128), default='', server_default=text("''"))
    # 主被叫之间的通话时长，单位为秒
    call_duration = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 计费时长，单位为秒
    bill_duration = db.Column(db.Integer, default=0, server_default=text("'0'"))

    # 企业本次通话唯一标识 id
    recorder_id = db.Column(db.String(256), default='', server_default=text("''"))
    # 通话录音 URL
    record_file_url = db.Column(db.String(1024), default='', server_default=text("''"))

    # 参数签名
    sign = db.Column(db.String(128), default='', server_default=text("''"))
    # 企业 id
    corp_key = db.Column(db.String(128), default='', server_default=text("''"))
    # unix 时间戳，精确度:秒
    ts = db.Column(db.String(128), default='', server_default=text("''"))

    # 主叫拨通虚拟号码时刻，格式为YYYY-MM-DD HH:mm:ss
    begin_time = db.Column(db.DateTime(), nullable=True)
    # 被叫振铃时间
    alerting_time = db.Column(db.DateTime(), nullable=True)
    # 被叫接通时刻
    connect_time = db.Column(db.DateTime(), nullable=True)
    # 通话结束时刻
    release_time = db.Column(db.DateTime(), nullable=True)

    # 扩展字段
    extend = db.Column(db.JSON, default={})

    # 是否外部工程商，用于记录客户资料是否外泄
    outer_user = db.Column(
        db.Integer, server_default=text("'0'"), default=0)

    @property
    def call_result_display(self):
        return CallResultDisplay.get(self.call_result, '未知')

    @property
    def outer_user_display(self):
        return Choice(self.outer_user).display


class CallOrderForYD(BaseModel):
    """
    add by zhouhl 2017-09-13
    移动话单推送
    """
    __tablename__ = 'call_order_for_yd'

    filter_dict = {
        'outer_user': (),
        'caller': (),
        'called': (),
        'call_result': (),
    }

    # 主叫真实号码
    caller = db.Column(db.String(128), default='', server_default=text("''"))
    # 主叫分配号码，即被叫看到的号显
    caller_show = db.Column(db.String(128), default='', server_default=text("''"))
    # 被叫真实号码
    called = db.Column(db.String(128), default='', server_default=text("''"))
    # 被叫分配号码，即主叫所拨打的虚拟号
    called_show = db.Column(db.String(128), default='', server_default=text("''"))
    # 主被叫之间的通话时长，单位为秒
    call_duration = db.Column(db.Integer, default=0, server_default=text("'0'"))
    # 通话状态，通话状态的取值请查通话状态区分
    call_result = db.Column(db.String(128), default='', server_default=text("''"))

    # 绑定关系 ID
    bind_id = db.Column(db.String(128), default='', server_default=text("''"))
    # 企业本次通话唯一标识 id
    recorder_id = db.Column(db.String(128), default='', server_default=text("''"))
    # 通话录音 URL
    record_file_url = db.Column(db.String(1024), default='', server_default=text("''"))

    # 参数签名
    sign = db.Column(db.String(128), default='', server_default=text("''"))
    # 企业 id
    corp_key = db.Column(db.String(128), default='', server_default=text("''"))
    # unix 时间戳，精确度:秒
    ts = db.Column(db.String(128), default='', server_default=text("''"))

    # 主叫拨通虚拟号码时刻，格式为YYYY-MM-DD HH:mm:ss
    begin_time = db.Column(db.DateTime(), nullable=True)
    # 被叫振铃时间
    alerting_time = db.Column(db.DateTime(), nullable=True)
    # 被叫接通时刻
    connect_time = db.Column(db.DateTime(), nullable=True)
    # 通话结束时刻
    release_time = db.Column(db.DateTime(), nullable=True)

    # 是否外部工程商，用于记录客户资料是否外泄
    outer_user = db.Column(
        db.Integer, server_default=text("'0'"), default=0)

    @property
    def call_result_display(self):
        return CallResultDisplay.get(self.call_result, '未知')

    @property
    def outer_user_display(self):
        return Choice(self.outer_user).display


class QueryCallNumberRecord(BaseModel):
    """
    add by zhouhl 2017-10-10
    联通呼叫号码查询记录
    """
    __tablename__ = 'query_call_number_records'

    filter_dict = {
        'caller': (),
        'called': (),
        'created_at_begin': ('created_at', '>='),
        'created_at_end': ('created_at', '<='),
    }

    # 企业ID
    corp_key = db.Column(db.String(128), default='', server_default=text("''"))
    # 时间戳
    ts = db.Column(db.String(128), default='', server_default=text("''"))
    # 参数签名
    sign = db.Column(db.String(128), default='', server_default=text("''"))
    # 被叫虚拟号
    called = db.Column(db.String(32), default='', server_default=text("''"))
    # 主叫真实号
    caller = db.Column(db.String(32), default='', server_default=text("''"))
    # 企业本次通话唯一标识ID
    recorder_id = db.Column(db.String(128), default='', server_default=text("''"))
    # 返回结果code
    return_code = db.Column(db.String(32), default='', server_default=text("''"))
    # 返回真实号码
    return_tel = db.Column(db.String(32), default='', server_default=text("''"))
    # 返回信息
    return_message = db.Column(db.String(128), default='', server_default=text("''"))

    # 通过recorder_id将QueryCallNumberRecord和CallOrderForLT关联起来
    call_order = db.relationship(
        'CallOrderForLT',
        primaryjoin='foreign(QueryCallNumberRecord.recorder_id) == remote(CallOrderForLT.recorder_id)',
        single_parent=True,
        backref=db.backref('query_record', uselist=False),
    )


class QuerySmsNumberRecord(BaseModel):
    """
    add by zhouhl 2017-10-10
    联通短信号码查询记录
    """
    __tablename__ = 'query_sms_number_records'

    filter_dict = {
        'receiver': (),
        'sender': (),
        'created_at_begin': ('created_at', '>='),
        'created_at_end': ('created_at', '<='),
    }


    # 企业ID
    corp_key = db.Column(db.String(128), default='', server_default=text("''"))
    # 时间戳
    ts = db.Column(db.String(128), default='', server_default=text("''"))
    # 参数签名
    sign = db.Column(db.String(128), default='', server_default=text("''"))
    # 接收虚拟号
    receiver = db.Column(db.String(32), default='', server_default=text("''"))
    # 真实发送号
    sender = db.Column(db.String(32), default='', server_default=text("''"))
    # 短信唯一标识
    sms_id = db.Column(db.String(128), default='', server_default=text("''"))
    # 返回结果code
    return_code = db.Column(db.String(32), default='', server_default=text("''"))
    # 返回真实号码
    return_tel = db.Column(db.String(32), default='', server_default=text("''"))
    # 返回信息
    return_message = db.Column(db.String(128), default='', server_default=text("''"))


class SmsOrder(BaseModel):
    """
    add by zhouhl 2017-10-10
    联通短信推送记录
    """
    __tablename__ = 'sms_orders'

    filter_dict = {
        'sender': (),
        'receiver': (),
        'sms_result': (),
        'transfer_time_begin': ('transfer_time', '>='),
        'transfer_time_end': ('transfer_time', '<='),
    }

    # 发送者真实号码
    sender = db.Column(db.String(32), default='', server_default=text("''"), index=True)
    # 接收者真实号码
    receiver = db.Column(db.String(32), default='', server_default=text("''"), index=True)
    # 发送者分配号码
    sender_show = db.Column(db.String(32), default='', server_default=text("''"))
    # 接收者分配号码
    receiver_show = db.Column(db.String(32), default='', server_default=text("''"))

    # 短信唯一标识
    sms_id = db.Column(db.String(128), default='', server_default=text("''"))
    # 短信发送时刻
    transfer_time = db.Column(db.DateTime(), nullable=True)
    # 短信内容
    sms_content = db.Column(db.Text(), default='')
    # 发送结果
    sms_result = db.Column(db.String(32))

    # 参数签名
    sign = db.Column(db.String(128), default='', server_default=text("''"))
    # 企业 id
    corp_key = db.Column(db.String(128), default='', server_default=text("''"))
    # unix 时间戳，精确度:秒
    ts = db.Column(db.String(128), default='', server_default=text("''"))

    @property
    def sms_result_display(self):
        return VirtualSmsMessageResult(self.sms_result).display
